const mysql = require('mysql2/promise');

async function createNewUserTable() {
  try {
    // 创建连接
    const connection = await mysql.createConnection({
      host: '152.136.35.251',
      user: 'shequ',
      password: 'YFeNttKfCRFb6wGE',
      database: 'shequ',
      port: 3306
    });

    console.log('Connected to database successfully!');
    
    // 创建新的用户表
    const createTableSQL = `
      CREATE TABLE IF NOT EXISTS shequ_users (
        id BIGINT(20) NOT NULL AUTO_INCREMENT,
        username VARCHAR(50) NOT NULL COMMENT '用户名',
        email VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
        phone VARCHAR(20) DEFAULT NULL COMMENT '手机号',
        password VARCHAR(100) NOT NULL COMMENT '加密后的密码',
        salt VARCHAR(50) NOT NULL COMMENT '密码盐',
        avatar VARCHAR(255) DEFAULT NULL COMMENT '头像URL',
        status TINYINT(1) NOT NULL DEFAULT 1 COMMENT '状态: 0-禁用,1-正常',
        last_login_time DATETIME DEFAULT NULL COMMENT '最后登录时间',
        last_login_ip VARCHAR(50) DEFAULT NULL COMMENT '最后登录IP',
        login_count INT DEFAULT 0 COMMENT '登录次数',
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        PRIMARY KEY (id),
        UNIQUE KEY uk_username (username),
        UNIQUE KEY uk_email (email),
        UNIQUE KEY uk_phone (phone)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
    `;
    
    try {
      await connection.execute(createTableSQL);
      console.log('Successfully created new shequ_users table with enhanced schema');
    } catch (error) {
      console.error('创建新表失败:', error.message);
    }
    
    // 显示新表结构
    try {
      const [rows] = await connection.execute('DESCRIBE shequ_users');
      console.log('新的shequ_users表结构:');
      console.table(rows);
    } catch (error) {
      console.error('查询表结构失败:', error.message);
    }

    // 列出所有表
    try {
      const [tables] = await connection.execute('SHOW TABLES');
      console.log('数据库中的所有表:');
      console.table(tables);
    } catch (error) {
      console.error('查询所有表失败:', error.message);
    }

    // 关闭连接
    await connection.end();
  } catch (error) {
    console.error('数据库连接失败:', error.message);
  }
}

createNewUserTable(); 